using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.Json;
using System.Diagnostics;
using MySql.Data.MySqlClient;
using hao_Model;
using System.ComponentModel.DataAnnotations.Schema;

namespace databaseMovie
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 简洁调试日志封装
            static void Log(string message)
            {
                Console.WriteLine(message);
                Debug.WriteLine(message);
            }
            static void LogError(string message)
            {
                Console.Error.WriteLine(message);
                Debug.WriteLine(message);
            }
            // 使用写死的连接配置，不读取 appsettings.json
            const string host = "129.211.94.62";
            const int port = 3306;
            const string user = "root";
            const string password = "Ww623025.";
            const string database = "hao_cloud_dev";

            // 校验密码，避免空密码导致“using password: NO”
            if (string.IsNullOrWhiteSpace(password))
            {
                LogError("数据库密码未配置，请在命令行使用 -Password <pwd> 或在 appsettings.json 的 Connection.Password 中设置。");
                Environment.Exit(1);
            }

            // 打印目标连接（不显示密码内容，仅显示长度）
            Log($"目标: {host}:{port} 用户: {user} 库: {database} 密码长度: {password.Length}");

            // 先连接到服务器级别，确保目标数据库存在
            var serverConnStr = new MySqlConnectionStringBuilder
            {
                Server = host,
                Port = (uint)port,
                UserID = user,
                Password = password,
                CharacterSet = "utf8mb4",
                AllowUserVariables = true,
                SslMode = MySqlSslMode.Disabled
            }.ConnectionString;

            using (var conn = new MySqlConnection(serverConnStr))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    // 创建数据库（若不存在）
                    cmd.CommandText = $"CREATE DATABASE IF NOT EXISTS `{database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
                    cmd.ExecuteNonQuery();
                    Log($"已确保数据库 `{database}` 存在");
                }
            }

            // 连接到目标数据库进行表结构同步
            var dbConnStr = new MySqlConnectionStringBuilder
            {
                Server = host,
                Port = (uint)port,
                UserID = user,
                Password = password,
                Database = database,
                CharacterSet = "utf8mb4",
                AllowUserVariables = true,
                SslMode = MySqlSslMode.Disabled
            }.ConnectionString;

            // 扫描 hao_Model 程序集中的实体类
            var asm = typeof(User).Assembly;
            var modelTypes = asm.GetTypes()
                .Where(t => t.IsClass && t.Namespace == "hao_Model" && !t.IsAbstract)
                .ToList();
            Log($"扫描实体类型数量: {modelTypes.Count}");

            using (var conn = new MySqlConnection(dbConnStr))
            {
                conn.Open();
                foreach (var t in modelTypes)
                {
                    // 按注解/约定生成建表 SQL 并执行
                    var tableName = CodeFirst.GetTableName(t);
                    var createSql = CodeFirst.BuildCreateTableSql(t);
                    Log($"同步表: {tableName}");
                    using (var cmd = new MySqlCommand(createSql, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    // 生成常规索引（例如 *Id、UserName/Mobile/Email）
                    var idxSqls = CodeFirst.BuildIndexes(t, tableName);
                    var created = 0;
                    foreach (var idx in idxSqls)
                    {
                        try
                        {
                            using var idxCmd = new MySqlCommand(idx, conn);
                            idxCmd.ExecuteNonQuery();
                            created++;
                        }
                        catch (Exception ex)
                        {
                            Debug.WriteLine($"创建索引失败 : {ex.Message}");
                        }
                    }
                    Log($"索引创建完成: {created}/{idxSqls.Count}");
                }
                // 插入基础种子数据（仅在不存在时插入）
                SeedData(conn);
            }

            Log("Code-First 表结构同步完成。");
        }

        static Dictionary<string, string> LoadConfig(string[] args)
        {
            // 解析命令行参数：-Key Value 形式
            var dict = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
            for (int i = 0; i < args.Length; i++)
            {
                var a = args[i];
                if (a.StartsWith("-"))
                {
                    var key = a.TrimStart('-');
                    if (i + 1 < args.Length && !args[i + 1].StartsWith("-"))
                    {
                        dict[key] = args[i + 1];
                        i++;
                    }
                    else
                    {
                        dict[key] = "true";
                    }
                }
            }

            // 从 appsettings.json 读取默认连接配置（多路径容错）
            var candidates = new List<string>
            {
                Path.Combine(AppContext.BaseDirectory, "appsettings.json"),
                Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json")
            };
            foreach (var jsonPath in candidates.Distinct())
            {
                if (File.Exists(jsonPath))
                {
                    try
                    {
                        using var fs = File.OpenRead(jsonPath);
                        using var doc = JsonDocument.Parse(fs);
                        if (doc.RootElement.TryGetProperty("Connection", out var conn))
                        {
                            foreach (var p in conn.EnumerateObject())
                            {
                                if (!dict.ContainsKey(p.Name))
                                {
                                    string? v = p.Value.ValueKind switch
                                    {
                                        JsonValueKind.String => p.Value.GetString(),
                                        JsonValueKind.Number => p.Value.TryGetInt64(out var n) ? n.ToString() : p.Value.GetRawText(),
                                        JsonValueKind.True => "true",
                                        JsonValueKind.False => "false",
                                        _ => p.Value.GetString() ?? p.Value.GetRawText()
                                    };
                                    dict[p.Name] = v ?? string.Empty;
                                }
                            }
                        }
                        Debug.WriteLine($"已读取配置: {jsonPath}");
                        break;
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine($"读取配置失败: {jsonPath} => {ex.Message}");
                    }
                }
            }
            return dict;
        }

        // 基础种子数据插入
        static void SeedData(MySqlConnection conn)
        {
            // 角色（使用固定ID，避免重复插入）
            var roleAdminId = Guid.Parse("00000000-0000-0000-0000-000000000001");
            var roleUserId = Guid.Parse("00000000-0000-0000-0000-000000000002");
            UpsertById(conn, "role", roleAdminId.ToString(),
                "INSERT INTO `role` (`Id`,`Name`,`Description`,`IsDeleted`) VALUES (@Id,@Name,@Desc,0)",
                new Dictionary<string, object> { {"@Id", roleAdminId.ToString() }, {"@Name","Admin"}, {"@Desc","管理员角色"} });
            UpsertById(conn, "role", roleUserId.ToString(),
                "INSERT INTO `role` (`Id`,`Name`,`Description`,`IsDeleted`) VALUES (@Id,@Name,@Desc,0)",
                new Dictionary<string, object> { {"@Id", roleUserId.ToString() }, {"@Name","User"}, {"@Desc","普通用户角色"} });

            // 管理员用户（固定ID）
            var adminUserId = Guid.Parse("00000000-0000-0000-0000-0000000000A1");
            UpsertById(conn, "user", adminUserId.ToString(),
                "INSERT INTO `user` (`Id`,`UserName`,`Password`,`Avatar`,`Phone`,`Email`,`Gender`,`RegisterTime`,`Status`,`IsDeleted`) " +
                "VALUES (@Id,@UserName,@Password,@Avatar,@Phone,@Email,@Gender,@RegisterTime,@Status,0)",
                new Dictionary<string, object> {
                    {"@Id", adminUserId.ToString() },
                    {"@UserName","admin"},
                    {"@Password","admin123"},
                    {"@Avatar",""},
                    {"@Phone","13800000000"},
                    {"@Email","admin@example.com"},
                    {"@Gender", 1},
                    {"@RegisterTime", DateTime.UtcNow },
                    {"@Status", 0}
                });

            // 绑定管理员角色（固定ID）
            var adminUserRoleId = Guid.Parse("00000000-0000-0000-0000-0000000000B1");
            UpsertById(conn, "userrole", adminUserRoleId.ToString(),
                "INSERT INTO `userrole` (`Id`,`UserId`,`RoleId`,`IsDeleted`) VALUES (@Id,@UserId,@RoleId,0)",
                new Dictionary<string, object> { {"@Id", adminUserRoleId.ToString() }, {"@UserId", adminUserId.ToString() }, {"@RoleId", roleAdminId.ToString() } });

            // 买家用户（固定ID）
            var buyerUserId = Guid.Parse("00000000-0000-0000-0000-0000000000A2");
            UpsertById(conn, "user", buyerUserId.ToString(),
                "INSERT INTO `user` (`Id`,`UserName`,`Password`,`Avatar`,`Phone`,`Email`,`Gender`,`RegisterTime`,`Status`,`IsDeleted`) " +
                "VALUES (@Id,@UserName,@Password,@Avatar,@Phone,@Email,@Gender,@RegisterTime,@Status,0)",
                new Dictionary<string, object> {
                    {"@Id", buyerUserId.ToString() },
                    {"@UserName","buyer"},
                    {"@Password","buyer123"},
                    {"@Avatar",""},
                    {"@Phone","13900000000"},
                    {"@Email","buyer@example.com"},
                    {"@Gender", 2},
                    {"@RegisterTime", DateTime.UtcNow },
                    {"@Status", 0}
                });

            // 绑定买家为普通用户角色（固定ID）
            var buyerUserRoleId = Guid.Parse("00000000-0000-0000-0000-0000000000B2");
            UpsertById(conn, "userrole", buyerUserRoleId.ToString(),
                "INSERT INTO `userrole` (`Id`,`UserId`,`RoleId`,`IsDeleted`) VALUES (@Id,@UserId,@RoleId,0)",
                new Dictionary<string, object> { {"@Id", buyerUserRoleId.ToString() }, {"@UserId", buyerUserId.ToString() }, {"@RoleId", roleUserId.ToString() } });

            // 分类
            UpsertById(conn, "category", "cat_elec",
                "INSERT INTO `category` (`Id`,`Name`,`ParentId`,`Sort`,`IsDeleted`) VALUES (@Id,@Name,@ParentId,@Sort,0)",
                new Dictionary<string, object> { {"@Id","cat_elec"}, {"@Name","电子数码"}, {"@ParentId","0"}, {"@Sort", 10 } });
            UpsertById(conn, "category", "cat_books",
                "INSERT INTO `category` (`Id`,`Name`,`ParentId`,`Sort`,`IsDeleted`) VALUES (@Id,@Name,@ParentId,@Sort,0)",
                new Dictionary<string, object> { {"@Id","cat_books"}, {"@Name","图书"}, {"@ParentId","0"}, {"@Sort", 20 } });
            UpsertById(conn, "category", "cat_cloth",
                "INSERT INTO `category` (`Id`,`Name`,`ParentId`,`Sort`,`IsDeleted`) VALUES (@Id,@Name,@ParentId,@Sort,0)",
                new Dictionary<string, object> { {"@Id","cat_cloth"}, {"@Name","服饰"}, {"@ParentId","0"}, {"@Sort", 30 } });

            // 示例商品（由管理员发布）
            var productId = Guid.NewGuid();
            UpsertById(conn, "product", productId.ToString(),
                "INSERT INTO `product` (`Id`,`UserId`,`Title`,`Description`,`Category`,`Price`,`OriginPrice`,`DeliveryMethod`,`BargainOption`,`Location`,`AutoRenew`,`CreatedAt`,`IsDeleted`) " +
                "VALUES (@Id,@UserId,@Title,@Description,@Category,@Price,@OriginPrice,@DeliveryMethod,@BargainOption,@Location,@AutoRenew,@CreatedAt,0)",
                new Dictionary<string, object> {
                    {"@Id", productId.ToString() },
                    {"@UserId", adminUserId.ToString() },
                    {"@Title","二手耳机 Sony WH-1000XM4"},
                    {"@Description","成色九成新，功能正常，支持面交或邮寄"},
                    {"@Category","电子数码"},
                    {"@Price", 1299.00m },
                    {"@OriginPrice", 2399.00m },
                    {"@DeliveryMethod", 3 }, // Both
                    {"@BargainOption", 3 }, // Negotiable
                    {"@Location","深圳"},
                    {"@AutoRenew", false },
                    {"@CreatedAt", DateTime.UtcNow }
                });
        }

        // 若主键不存在则插入
        static void UpsertById(MySqlConnection conn, string table, string id,
            string insertSql, Dictionary<string, object> parameters)
        {
            var checkSql = $"SELECT COUNT(1) FROM `{table}` WHERE `Id` = @Id";
            using var check = new MySqlCommand(checkSql, conn);
            check.Parameters.AddWithValue("@Id", id);
            var exists = Convert.ToInt32(check.ExecuteScalar()) > 0;
            if (exists) return;
            using var cmd = new MySqlCommand(insertSql, conn);
            foreach (var kv in parameters)
            {
                cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
            }
            cmd.ExecuteNonQuery();
        }
    }

    static class CodeFirst
    {
        // 大文本字段名约定（命名符合这些值时使用 TEXT）
        static readonly HashSet<string> LargeTextNames = new(StringComparer.OrdinalIgnoreCase)
        {
            "content","description","remark","body","details","summary","message"
        };

        public static string GetTableName(Type modelType)
        {
            // 表名解析：优先 [Table(Name)]，否则类名小写
            var tableAttr = modelType.GetCustomAttributes(true)
                .OfType<TableAttribute>()
                .FirstOrDefault();
            if (tableAttr != null && !string.IsNullOrWhiteSpace(tableAttr.Name))
                return tableAttr.Name.Trim();
            return modelType.Name.ToLowerInvariant();
        }

        public static string BuildCreateTableSql(Type modelType)
        {
            // 生成 CREATE TABLE 语句（处理主键、必填、类型映射）
            var tableName = GetTableName(modelType);
            var props = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(p => !IsNotMapped(p))
                .ToList();

            var columnsSql = new List<string>();
            string? pkColumn = null;
            foreach (var p in props)
            {
                var colDef = MapClrTypeToMySql(p);
                var isRequired = IsRequired(p);
                var columnAttr = p.GetCustomAttributes(true)
                    .OfType<ColumnAttribute>()
                    .FirstOrDefault();
                var colName = !string.IsNullOrWhiteSpace(columnAttr?.Name) ? columnAttr!.Name! : p.Name;

                var parts = new List<string> { $"`{colName}` {colDef}" };
                if (isRequired) parts.Add("NOT NULL");

                if (IsPrimaryKey(p) || string.Equals(p.Name, "Id", StringComparison.OrdinalIgnoreCase))
                {
                    // 整型主键自增，非整型主键强制 NOT NULL
                    pkColumn = colName;
                    if (p.PropertyType == typeof(int) || p.PropertyType == typeof(long)
                        || p.PropertyType == typeof(int?) || p.PropertyType == typeof(long?))
                    {
                        parts.Add("AUTO_INCREMENT");
                    }
                    else
                    {
                        parts.Add("NOT NULL");
                    }
                }

                columnsSql.Add(string.Join(' ', parts));
            }

            var pkSql = pkColumn != null ? $", PRIMARY KEY (`{pkColumn}`)" : string.Empty;
            var createSql =
                $"CREATE TABLE IF NOT EXISTS `{tableName}` (\n  {string.Join(",\n  ", columnsSql)}{pkSql}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";
            return createSql;
        }

        public static List<string> BuildIndexes(Type modelType, string? overrideTableName = null)
        {
            // 按约定生成普通索引：*Id 及常用查询字段
            var tableName = overrideTableName ?? GetTableName(modelType);
            var props = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(p => !IsNotMapped(p))
                .ToList();

            var indexSqls = new List<string>();
            foreach (var p in props)
            {
                var columnAttr = p.GetCustomAttributes(true)
                    .OfType<ColumnAttribute>()
                    .FirstOrDefault();
                var colName = !string.IsNullOrWhiteSpace(columnAttr?.Name) ? columnAttr!.Name! : p.Name;

                if (!string.Equals(colName, "Id", StringComparison.OrdinalIgnoreCase) &&
                    colName.EndsWith("Id", StringComparison.OrdinalIgnoreCase))
                {
                    var idxName = $"idx_{tableName}_{colName}".ToLowerInvariant();
                    indexSqls.Add($"CREATE INDEX `{idxName}` ON `{tableName}` (`{colName}`);");
                }

                if (string.Equals(colName, "UserName", StringComparison.OrdinalIgnoreCase) ||
                    string.Equals(colName, "Mobile", StringComparison.OrdinalIgnoreCase) ||
                    string.Equals(colName, "Email", StringComparison.OrdinalIgnoreCase))
                {
                    var idxName = $"idx_{tableName}_{colName}".ToLowerInvariant();
                    indexSqls.Add($"CREATE INDEX `{idxName}` ON `{tableName}` (`{colName}`);");
                }
            }
            return indexSqls;
        }

        static bool IsPrimaryKey(PropertyInfo p)
            => p.GetCustomAttributes(true).Any(a => a.GetType().Name == "KeyAttribute");

        static bool IsNotMapped(PropertyInfo p)
            => p.GetCustomAttributes(true).Any(a => a.GetType().Name == "NotMappedAttribute");

        static bool IsRequired(PropertyInfo p)
            => p.GetCustomAttributes(true).Any(a => a.GetType().Name == "RequiredAttribute");

        static int? GetMaxLength(PropertyInfo p)
        {
            // 解析字符串长度注解：MaxLength / StringLength
            var maxAttr = p.GetCustomAttributes(true).FirstOrDefault(a => a.GetType().Name == "MaxLengthAttribute");
            var strAttr = p.GetCustomAttributes(true).FirstOrDefault(a => a.GetType().Name == "StringLengthAttribute");
            int? len = null;
            if (maxAttr != null) len = (int?)maxAttr.GetType().GetProperty("Length")?.GetValue(maxAttr);
            if (strAttr != null) len = (int?)strAttr.GetType().GetProperty("MaximumLength")?.GetValue(strAttr) ?? len;
            return len;
        }

        static (int precision, int scale)? GetDecimalSpec(PropertyInfo p)
        {
            // 解析 Column(TypeName="decimal(p,s)") 精度与小数位
            var colAttr = p.GetCustomAttributes(true)
                .OfType<ColumnAttribute>()
                .FirstOrDefault();
            if (!string.IsNullOrWhiteSpace(colAttr?.TypeName))
            {
                var tn = colAttr.TypeName!.Trim();
                var m = System.Text.RegularExpressions.Regex.Match(tn, @"decimal\((\d+),\s*(\d+)\)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                if (m.Success) return (int.Parse(m.Groups[1].Value), int.Parse(m.Groups[2].Value));
            }
            return null;
        }

        static string MapClrTypeToMySql(PropertyInfo p)
        {
            // CLR 类型到 MySQL 列类型的映射（优先尊重 Column(TypeName)）
            var t = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType;
            var colAttr = p.GetCustomAttributes(true)
                .OfType<ColumnAttribute>()
                .FirstOrDefault();
            if (!string.IsNullOrWhiteSpace(colAttr?.TypeName))
            {
                var typeName = colAttr!.TypeName!.Trim();
                if (typeName.Equals("varchar", StringComparison.OrdinalIgnoreCase))
                {
                    var len = GetMaxLength(p) ?? 255;
                    return $"VARCHAR({len})";
                }
                return typeName.ToUpperInvariant();
            }

            if (t == typeof(string))
            {
                var len = GetMaxLength(p);
                if (len.HasValue && len.Value > 0)
                    return $"VARCHAR({len.Value})";
                if (LargeTextNames.Contains(p.Name))
                    return "TEXT";
                return "VARCHAR(255)";
            }
            if (t == typeof(Guid)) return "CHAR(36)";
            if (t == typeof(bool)) return "TINYINT(1)";
            if (t == typeof(byte)) return "TINYINT UNSIGNED";
            if (t == typeof(short)) return "SMALLINT";
            if (t == typeof(int)) return "INT";
            if (t == typeof(long)) return "BIGINT";
            if (t == typeof(float)) return "FLOAT";
            if (t == typeof(double)) return "DOUBLE";
            if (t == typeof(decimal))
            {
                var spec = GetDecimalSpec(p) ?? (18, 2);
                return $"DECIMAL({spec.precision},{spec.scale})";
            }
            if (t == typeof(DateTime)) return "DATETIME";
            if (t == typeof(DateTimeOffset)) return "DATETIME";
            if (t.IsEnum) return "INT";
            return "JSON";
        }
    }
}
